package org.trialiet.notedemo.util;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by Trialiet on 2016/4/27.
 */
public class DBAdapter{
    private static String CREATE_TABLE;
    private static final String TABLE_USER = "user";
    private static final String TABLE_NOTE = "note";
    private static final String KEY_TITLE = "title";
    private static final String KEY_CONTENT = "content";
    private static final String KEY_ID = "note_id";
    private static final String KEY_FK = "user";
    private static final String DATABASE_NAME = "IceNoteDB";
    private static final String USER_ID = "user_id";
    private static final String USER_NAME = "username";
    private static final String USER_PASSWORD = "password";
    private final Context context;
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    public DBAdapter(Context ctx){
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

    private static class DatabaseHelper extends SQLiteOpenHelper{
        DatabaseHelper(Context context){
            super(context, DATABASE_NAME, null, 1);
        }

        @Override
        public void onCreate(SQLiteDatabase database) {
            try {
                database.execSQL("CREATE TABLE "+ TABLE_USER + " ( " +
                        USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        USER_NAME + " TEXT, " +
                        USER_PASSWORD + " TEXT " +
                        ");");
                database.execSQL("CREATE TABLE "+ TABLE_NOTE + " ( " +
                        KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        KEY_TITLE + " TEXT, " +
                        KEY_CONTENT + " TEXT, " +
                        KEY_FK + " INTEGER, " +
                        "FOREIGN KEY("+ KEY_FK + ") REFERENCES " + TABLE_USER + "(" + USER_ID + ") " +
                        ");");
                database.execSQL("CREATE TRIGGER fk BEFORE INSERT ON note FOR EACH ROW BEGIN " +
                        "SELECT CASE WHEN ((SELECT user_id FROM user WHERE user_id = new.user) IS NULL) " +
                        "THEN RAISE (ABORT, 'Foreign Key Violation') END; END;");
            }catch (SQLException e){
                e.printStackTrace();
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NOTE);
            db.execSQL("DROP TRIGGER IF EXISTS fk");
            onCreate(db);
        }
    }

    public DBAdapter open() throws SQLException{
        db = DBHelper.getWritableDatabase();
        return this;
    }

    public void close(){
        DBHelper.close();
    }

    public long insertNote(String title, String content){
        ContentValues values = new ContentValues();
        values.put(KEY_TITLE, title);
        values.put(KEY_CONTENT, content);
        values.put(KEY_FK, SharedPreferenceUtil.getLoginUser(context));
        return db.insert(TABLE_NOTE, null, values);
    }

    public boolean deleteNote(long id){
        return db.delete(TABLE_NOTE, KEY_ID + "=" + id, null) > 0;
    }

    public List<Note> getAllNotes(){
        List<Note> list = new ArrayList<>();
        String title, content;
        long id;
        long user_id;
        Cursor cursor = db.query(TABLE_NOTE, new String[]{KEY_ID, KEY_TITLE, KEY_CONTENT, KEY_FK},
                KEY_FK + "=" + SharedPreferenceUtil.getLoginUser(context), null, null, null, null, null);
        if (!cursor.moveToFirst()) {
            return null;
        } else
            do {
                title = cursor.getString(cursor.getColumnIndex(KEY_TITLE));
                content = cursor.getString(cursor.getColumnIndex(KEY_CONTENT));
                id = cursor.getInt(cursor.getColumnIndex(KEY_ID));
                user_id = cursor.getInt(cursor.getColumnIndex(KEY_FK));
                list.add(new Note(id, title, content, user_id));
            } while (cursor.moveToNext());
        cursor.close();
        return list;
    }

    public Note getNote(long id) throws SQLException{
        Cursor cursor = db.query(true, TABLE_NOTE, new String[] {KEY_ID, KEY_TITLE, KEY_CONTENT, KEY_FK},
                KEY_ID + "=" + id + " and " + KEY_FK + "=" + SharedPreferenceUtil.getLoginUser(context), null, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();
        String title = cursor.getString(cursor.getColumnIndex(KEY_TITLE));
        String content = cursor.getString(cursor.getColumnIndex(KEY_CONTENT));
        long _id = cursor.getInt(cursor.getColumnIndex(KEY_ID));
        long user_id = cursor.getInt(cursor.getColumnIndex(KEY_FK));
        Note note = new Note(_id, title, content, user_id);
        cursor.close();
        return note;
    }

    public boolean updateNote(long id, String title, String content){
        ContentValues values = new ContentValues();
        values.put(KEY_TITLE, title);
        values.put(KEY_CONTENT, content);
        return db.update(TABLE_NOTE, values, KEY_ID + "=" + id, null) > 0;
    }

    public long insertUser(String username, String password){
        if(getUser(username,password) >= 0){
            return -1;
        }
        ContentValues values = new ContentValues();
        values.put(USER_NAME, username);
        values.put(USER_PASSWORD, password);
        return db.insert(TABLE_USER, null, values);
    }

    public boolean deleteUser(long id){
        return db.delete(TABLE_USER, USER_ID + "=" + id, null) > 0;
    }

    public long getUser(String username, String password) throws SQLException{
        Cursor cursor = db.query(true, TABLE_USER, new String[] {USER_ID, USER_NAME, USER_PASSWORD},
                USER_NAME + "=" + "'" + username + "'" + " and " + USER_PASSWORD + "=" + "'" + password + "'", null, null, null, null, null);
        if (!cursor.moveToFirst()){
            cursor.close();
            return -1;
        }
        long user_id = cursor.getInt(cursor.getColumnIndex(USER_ID));
        cursor.close();
        return user_id;
    }
}
